package com.df.springmvc.system.user.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.stereotype.Repository;

import com.common.bean.SQLInsertBean;
import com.common.bean.SQLUpdateBean;
import com.df.springmvc.supper.SupperDao;


@Repository
public class UserDao extends SupperDao{
	private String message;
	
	/**
	 * *
	 * @param sql
	 *            查询的SQL
	 * @param param_HashMap
	 *            查询参数
	 * @param i
	 *            从第几天开始
	 * @param j
	 *            显示多少条数据
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public ArrayList<Object> query(HashMap<String,String> map,Object[] attribute,int i,int j) throws Exception{
		// TODO Auto-generated method stub
		Session session=getSessionFactory().getCurrentSession();
		String sql="select '' as 'ck',guid,userAccount,userName,(case when accountStatus='1' then '正常' else '已冻结' end  ) as accountStatus,userType,(case when userType='1' then '公司用户' else '供应商用户' end  ) as userTypeName,(select v.userGroupName from  v_usergroupToGroupName v where v.userAccount =s.userAccount ) as UserGroupName from s_users_info s where 1=1 and status = '1'  ";
		if(map.get("userAccount") != null && !"".equals(map.get("userAccount"))){
			sql=sql + " and userAccount LIKE :s  ";
		}
		if(map.get("userName") != null && !"".equals(map.get("userName"))){
			sql=sql + " and userName LIKE :userName ";
		}
		if(map.get("userType") != null && !"".equals(map.get("userType"))){
			sql=sql + " and userType=:userType ";
		}
		sql = sql+" order by createTime desc";
		SQLQuery query=session.createSQLQuery(sql);
		if(map.get("userAccount") != null && !"".equals(map.get("userAccount"))){
			query.setParameter("s","%" + map.get("userAccount") + "%");
		}
		if(map.get("userName") != null && !"".equals(map.get("userName"))){
			query.setParameter("userName","%" + map.get("userName") + "%");
		}
		if(map.get("userType") != null && !"".equals(map.get("userType"))){
			query.setParameter("userType",map.get("userType"));
		}
		query.setFirstResult(i);
		query.setMaxResults(j);
		return (ArrayList<Object>)query.list();
	}
	/**
	 * *
	 * 添加的时候验证重复
	 * @param map
	 * @return
	 */
	public Object queryVerify(HashMap<String,String> map){
		Session session=getSessionFactory().getCurrentSession();
		String sql="select 0 from s_users_info where userAccount=:userAccount and STATUS = 1";
		SQLQuery query=session.createSQLQuery(sql);
		query.setString("userAccount",map.get("userAccount"));
		if(query.list().size() > 0){
			message="有重复的用户账号，请重新输入！";
			return message;
		}
		return null;
	}
	/**
	 * 修改的时候验重
	 * @param map
	 * @return
	 */
	public Object queryVerifyUpd(HashMap<String,String> map){
		Session session=getSessionFactory().getCurrentSession();
		String sql="select 0 from s_users_info where userAccount=:userAccount and STATUS = 1 and guid not in (:gu)";
		SQLQuery query=session.createSQLQuery(sql);
		query.setString("userAccount",map.get("userAccount"));
		query.setString("gu",map.get("guid"));
		if(query.list().size() > 0){
			message="有重复的用户账号，请重新输入！";
			return message;
		}
		return null;
	}
	
	public Object query(HashMap<String,String> map){
		// TODO Auto-generated method stub
		Session session=getSessionFactory().getCurrentSession();
		String sql="select * from s_users_info where 1=1 and STATUS = 1 and guid=:g";
		SQLQuery query=session.createSQLQuery(sql);
		query.setString("g",map.get("guid"));
		return query.list();
	}
	@Override
	public Object add(HashMap<String,Object> map) throws Exception{
		// TODO Auto-generated method stub
		SQLInsertBean insertBean=new SQLInsertBean("s_users_info");
		HashMap<String,Object> columnMap=new HashMap<String,Object>(map);
		insertBean.setColumnMap(columnMap);
		Object o=excuteSqlBean(insertBean);
		return o;
	}
	/**
	 * 关联用户组
	 * @param map
	 * @param list
	 * @return
	 * @throws Exception
	 */
	public Object addGroup(HashMap<String, String> map,ArrayList<String[]> list) throws Exception {
		// TODO Auto-generated method stub
		String result = "fail";
		int num=0;
		String userAccount = (String) map.get("userAccount");
		ArrayList<Object> listQuery = (ArrayList<Object>) VerifyUserGroup(userAccount);
		int len = listQuery.size();
		if (len == 0) {
			result = "success";
		} else {
			Object o = delGroup(userAccount);
			if ((len + "").equals(o)) {
				result = "success";
			}
		}
		if (result == "success") {
			if(list.size()!=0){
				String[] arr = { "guid", "status", "createBy", "createTime","userAccount","userGroupCode"};
				for (String[] s : list) {
					HashMap<String, Object> hashMap = new HashMap<String, Object>();
					for (int i = 0; i < arr.length; i++) {
						hashMap.put(arr[i], s[i]);
					}
					Object o = addGroup2(hashMap);
					if ("1".equals(o)) {
						num++;
					} else {
						break;
					}
				}
			}else{
				num=1;
			}
		}else{
			throw new RuntimeException("关联失败！");
		}
		return num;
		
	}
	@Override
	public Object upd(HashMap<String,Object> map) throws Exception{
		// TODO Auto-generated method stub
		HashMap<String,Object> tmp=new HashMap<String,Object>();
		tmp.put("guid",map.get("guid"));
		Object rs=excuteSqlBean(new SQLUpdateBean("s_users_info",map,tmp){});
		return rs;
	}
	@Override
	public HashMap<String,Object> view(String guid) throws Exception{
		// TODO Auto-generated method stub
		//String sql="select * from s_users_info where guid=:guid";
		/*String sql="select s.*,b.staffName from s_users_info s left join s_staff_info b on b.staffId=s.staffCode where s.guid =:guid";*/
		String sql="select s.* from s_users_info s  where s.guid =:guid";
		HashMap<String,Object> tmp=new HashMap<String,Object>();
		tmp.put("guid",guid);
		ArrayList<HashMap<String,Object>> list=querySqlToSQLResultBean(sql,tmp,0,0).getMapList();
		if(list.size() == 1){
			return list.get(0);
		}else{
			throw new Exception("找不到数据");
		}
	}
	@Override
	public Object del(HashMap<String,Object> map) throws Exception{
		// TODO Auto-generated method stub
		String result = "fail";
		String userAccount = (String) map.get("userAccount");
		map.remove("userAccount");
		HashMap<String, Object> tmp = new HashMap<String, Object>();
		tmp.put("guid", map.get("guid"));
		Object rs = excuteSqlBean(new SQLUpdateBean("s_users_info", map, tmp) {});
		if("1".equals(rs)){
			ArrayList<Object> listQuery = (ArrayList<Object>) VerifyUserGroup(userAccount);
			int len = listQuery.size();
			if (len == 0) {
				result = "success";
			} else {
				Object o = delGroup(userAccount);
				if ((len + "").equals(o)) {
					result = "success";
				}
			}
		}
		if(result=="success"){
			return rs;
		}else{
			throw new RuntimeException("删除失败");
		}
	}
	
	public Object addGroup2(HashMap<String, Object> map) throws Exception {
		// TODO Auto-generated method stub
		SQLInsertBean insertBean=new SQLInsertBean("sr_user_group");
		HashMap<String,Object> columnMap=new HashMap<String,Object>(map);
		insertBean.setColumnMap(columnMap);
		Object resulto=excuteSqlBean(insertBean);
		return resulto;
	}
	public Object delGroup(String userAccount) throws Exception {
		// TODO Auto-generated method stub
		String sql1 = "delete from sr_user_group where userAccount = ?";
		Object o = updateSql(sql1, new Object[] { userAccount });
		return o;
	}
	public boolean check(String guid) throws Exception {
		// TODO Auto-generated method stub
		String sql="select * from s_users_info where guid=:guid";
		HashMap<String,Object> tmp=new HashMap<String,Object>();
		tmp.put("guid",guid);
		ArrayList<HashMap<String,Object>> list=querySqlToSQLResultBean(sql,tmp,0,0).getMapList();
		if(list.size() == 0){
			return true;
		}else{
			return false;
		}
	}
	public String getMessage(){
		return message;
	}
	public void setMessage(String message){
		this.message=message;
	}
	public List VerifyUserGroup(String userAccount) {
		// TODO Auto-generated method stub
		Session session=getSessionFactory().getCurrentSession();
		String sql="select guid from sr_user_group where userAccount=:userAccount and STATUS = 1 ";
		SQLQuery query=session.createSQLQuery(sql);
		query.setString("userAccount",userAccount);
		return query.list();
	}
	
	
	/**
	 * 获取人员信息
	 * @param guid 
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public ArrayList<HashMap<String, Object>> getStaff(String guid) throws Exception {
		Session session = getSessionFactory().getCurrentSession();
		String sqlStaff = "select staffCode from s_users_info where 1=1 and status=1 and staffCode not in(select staffCode from s_users_info where 1=1 and guid='"+guid+"')";
		// ArrayList<String> listStaff = (ArrayList<String>)querySql(sqlStaff);
		SQLQuery query = session.createSQLQuery(sqlStaff);
		ArrayList<String> listStaff = (ArrayList<String>) query.list();
		String ucode = "";
		String u = "";
		for (int i = 0; i < listStaff.size(); i++) {
			if (i == listStaff.size() - 1) {
				u = "'" + (String) listStaff.get(i) + "'";
			} else {
				u = "'" + (String) listStaff.get(i) + "'" + ",";
			}
			ucode += u;
		}
		String sql = "";
		if ("".equals(ucode)) {
			sql = "select staffId,staffName from s_staff_info where 1=1 and status=1 ";
		} else {
			sql = "select staffId,staffName from s_staff_info where 1=1 and status=1  and staffId not in("+ ucode + ") ";
		}
		return querySqlToSQLResultBean(sql).getMapList();

	}
	/**
	 * 获取未被关联的用户组信息
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HashMap<String, Object>> getUserGroup(String userAccount) throws Exception {
		Session session = getSessionFactory().getCurrentSession();
		String sqlStaff = "select userGroupCode from sr_user_group where 1=1 and status=1 and userAccount='"+userAccount+"'";
		// ArrayList<String> listStaff = (ArrayList<String>)querySql(sqlStaff);
		SQLQuery query = session.createSQLQuery(sqlStaff);
		ArrayList<String> listStaff = (ArrayList<String>) query.list();
		String ucode = "";
		String u = "";
		for (int i = 0; i < listStaff.size(); i++) {
			if (i == listStaff.size() - 1) {
				u = "'" + (String) listStaff.get(i) + "'";
			} else {
				u = "'" + (String) listStaff.get(i) + "'" + ",";
			}
			ucode += u;
		}
		String sql = "";
		if ("".equals(ucode)) {
			sql = "select userGroupCode,userGroupName from s_userGroup_info where 1=1 and status=1 ";
		} else {
			sql = "select userGroupCode,userGroupName from s_userGroup_info where 1=1 and status=1  and userGroupCode not in("+ ucode + ")";
		}
		return querySqlToSQLResultBean(sql).getMapList();
	}
	/**
	 * 该成员已关联的用户组
	 * @param userAccount
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HashMap<String, Object>> getUserTeamGroup(String userAccount) throws Exception {
		// TODO Auto-generated method stub
		String sql = "select s.userGroupCode,b.userGroupName from sr_user_group s left join s_userGroup_info b on  b.userGroupCode=s.userGroupCode where 1=1 and s.status=1 and s.userAccount='"+userAccount+"'";
		return querySqlToSQLResultBean(sql).getMapList();
		
	}
	public Object resetPassWord(HashMap<String, Object> map) throws Exception {
		// TODO Auto-generated method stub
		HashMap<String,Object> tmp=new HashMap<String,Object>();
		tmp.put("guid",map.get("guid"));
		Object rs=excuteSqlBean(new SQLUpdateBean("s_users_info",map,tmp){});
		return rs;
	}
	
	
	
	
}
